package com.cloud.drore.eboss.supplier.mapper.provider;

import com.cloud.drore.eboos.common.constant.CommonConstant;
import com.cloud.drore.eboss.operation.entity.po.DistributorInfoPo;
import com.cloud.drore.eboss.operation.entity.po.DistributorsAllRelationPo;

/**
 * 浙江卓锐科技股份有限公司 版权所有  Copyright 2018<br>
 *
 * @Description: <br>
 * @Project: eboos <br>
 * @CreateDate: Created in 2018/3/23 10:59 <br>
 * @Author: <a href="1451173193@qq.com">杨亮</a>
 */
public class SupplierRelationProvider {
    public String getAllDistributorBySupplierIdSql(Long supplierId){
        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT\n" +
                "\ta.id,\n" +
                "\ta.merchant_name distributor_name,\n" +
                "\ta.merchant_num distributor_num \n" +
                "\t FROM eboos_merchant a\n" +
                "INNER JOIN eboos_distributor_product_association b on a.id=b.distributor_id\n" +
                "INNER JOIN eboos_product_consume_relation c on b.main_id=c.product_id" +
                " where a.merchant_type='"+CommonConstant.MERCHANT_DISTRIBUTE+"' and c.merchant_id="+supplierId+" and a.merchant_state='"+ CommonConstant.MERCHANT_AUDIT_ALREADY+"'");
        sql.append(" GROUP BY a.id");
        return sql.toString();
    }
    public String getDistributorInfoListBySupplierSql(DistributorInfoPo po){
        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT\n" +
                "\ta.id,\n" +
                "\ta.merchant_name distributor_name,\n" +
                "\ta.merchant_num distributor_num,\n" +
                "\ta.link_man,a.mobile,\n" +
                "\tmerchant_industry distributor_type\n" +
                "\t FROM eboos_merchant a\n" +
                "INNER JOIN eboos_distributor_product_association b on a.id=b.distributor_id\n" +
                "INNER JOIN eboos_product_consume_relation c on b.main_id=c.product_id" +
                " where a.merchant_type='"+CommonConstant.MERCHANT_DISTRIBUTE+"' and c.merchant_id="+po.getSupplierId()+" and a.merchant_state='"+ CommonConstant.MERCHANT_AUDIT_ALREADY+"'");
        if (po.getDistributorName() != null && !po.getDistributorName().equals("")) {
            sql.append(" and instr(a.merchant_name,'" + po.getDistributorName() + "')>0");
        }
        if (po.getDistributorNum() != null && !po.getDistributorNum().equals("")) {
            sql.append("  and instr(a.merchant_num,'" + po.getDistributorNum() + "')>0");
        }
        sql.append(" GROUP BY a.id");
        return sql.toString();
    }
    public String getDistributorsAllRelationSql(DistributorsAllRelationPo po){
        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT a.main_id,b.created_date,b.product_num,b.product_name,\n" +
                "\tb.product_type product_type,\n" +
                "\ta.settlement_price,a.sales_price,\n" +
                "\ta.id relation_id,\n" +
                "\tGROUP_CONCAT(distinct e.consume_name) consume_name,\n" +
                "\tGROUP_CONCAT(distinct f.consume_poi_name) poi_name\n" +
                "FROM eboos_distributor_product_association a\n" +
                "INNER JOIN eboos_product b ON a.main_id = b.id\n" +
                "INNER JOIN eboos_merchant c ON c.id = a.distributor_id\n" +
                "INNER JOIN eboos_product_consume_relation d ON d.product_id = a.main_id\n" +
                "INNER JOIN eboos_product_consume_relation dd ON d.product_id = dd.product_id\n" +//再连一下该表不然如果是组合产品多个供应商，显示的产品信息不全（产品将不包括其他供应商的景点信息）
                "INNER JOIN eboos_consume e ON e.id = dd.consume_id\n" +
                "INNER JOIN eboos_consume f ON f.consume_poi_id = dd.poi_id" +
                " where c.merchant_state='"+CommonConstant.MERCHANT_AUDIT_ALREADY+"' and b.status='"+CommonConstant.THE_SHELVES+"' AND d.merchant_id = "+po.getSupplierId()+" and a.distributor_id=" + po.getDistributorId());
        if (po.getProductName() != null && !po.getProductName().equals("")) {
            sql.append(" and instr(b.product_name,'" + po.getProductName() + "')>0");
        }
        sql.append(" GROUP BY a.main_id");
        return sql.toString();
    }

}
